package com.wonders.hs.pd.mm.web.utils;

import com.wonders.hs.pd.mm.web.config.SystemConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.function.Consumer;

/**
 * @author banglong
 * @date 2017/3/13
 */
public class JDBCConnect {

  private static final Logger logger = LoggerFactory.getLogger(JDBCConnect.class);

  private static Connection getConnection() {
    Connection connection = null;
    try {
      // 加载驱动
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      // 链接
      connection = DriverManager.getConnection(SystemConfig.DB_URL, SystemConfig.DB_USERNAME, SystemConfig.DB_PASSWORD);

    } catch (Exception e) {
      logger.error("get connection fail!", e);
    }
    return connection;
  }

  /**
   * 执行通用sql的查询
   */
  private static List<Map<String, String>> handleQuery(String sql, Consumer<CallableStatement> consumer) {
    // 所有的结果集
    List<Map<String, String>> result = new ArrayList<>();
    ResultSet resultSet;
    try (Connection connection = getConnection();
         CallableStatement callableStatement = connection.prepareCall(sql)) {
      // 使用 lambda 表达式处理 sql 参数
      consumer.accept(callableStatement);
      // 执行结果
      callableStatement.execute();
      // 拿到查询结果
      resultSet = callableStatement.getResultSet();
      // 获取元数据
      ResultSetMetaData column = resultSet.getMetaData();
      while (resultSet.next()) {
        Map<String, String> map = new LinkedHashMap<>();
        for (int i = 1; i <= column.getColumnCount(); i++) {
          map.put(column.getColumnName(i), resultSet.getString(i));
        }
        result.add(map);
      }
    } catch (Exception e) {
      logger.error("查询失败，sql: " + sql, e);
    }
    return result;
  }

  /**
   * 供应商不良记录查询
   *
   * @param startTime 开始时间
   * @param endTime   结束时间
   * @return 查询结果
   */
  public static List<Map<String, String>> badRecordQuery(String startTime, String endTime) {
    String sql = "SET NOCOUNT ON exec prco_queryBlackRecord ?,?";
    List<Map<String, String>> maps = handleQuery(sql, callableStatement -> {
      try {
        callableStatement.setString(1, startTime);
        callableStatement.setString(2, endTime);
      } catch (SQLException e) {
        logger.error("查询失败，sql: " + sql, e);
      }
    });
    if (maps.size() == 0) {
      HashMap<String, String> map = new HashMap<>();
      map.put("查询结果", "暂无数据");
      maps.add(map);
    }
    return maps;
  }

  /**
   * 三证更新查询
   *
   * @param startTime 开始时间
   * @param endTime   结束时间
   * @return 查询结果
   */
  public static List<Map<String, String>> medicareCodeQuery(String startTime, String endTime, int count) {
    String sql = "SET NOCOUNT ON exec dbo.proc_wz_sz ?, ?, ?";
    List<Map<String, String>> maps = handleQuery(sql, callableStatement -> {
      try {
        callableStatement.setString(1, startTime);
        callableStatement.setString(2, endTime);
        callableStatement.setInt(3, count);
      } catch (SQLException e) {
        logger.error("查询失败，sql: " + sql, e);
      }
    });
    if (maps.size() == 0) {
      HashMap<String, String> map = new HashMap<>();
      map.put("查询结果", "暂无数据");
      maps.add(map);
    }
    return maps;
  }

  /**
   * 领用收货单查询
   *
   * @param id 开始时间
   * @return 查询结果
   */
  public static List<Map<String, String>> billQuery(String id) {
    String sql = "SET NOCOUNT ON exec dbo.proc_wz_Check_HelpNumber ?";
    return handleQuery(sql, callableStatement -> {
      try {
        callableStatement.setString(1, id);
      } catch (SQLException e) {
        logger.error("查询失败，sql: " + sql, e);
      }
    });
  }

//  public static List<Map<String, String>> balanceQuery(String deptAccountID, String startTime, String endTime) {
//
//    Connection connection = getConnection();
//    CallableStatement callableStatement = null;
//    ResultSet resultSet = null;
//
//    // 所有的结果集
//    List<Map<String, String>> result = new ArrayList<>();
//
//    if (connection != null) {
//
//      try {
//        // 执行存储过程
//        // 查询账户信息 测试
//        callableStatement = connection.prepareCall("SET NOCOUNT ON exec proc_wz_balancequery_single ?,?,?");
//        callableStatement.setString(1, deptAccountID);
//        callableStatement.setString(2, startTime);
//        callableStatement.setString(3, endTime);
//        callableStatement.execute();
//
//        resultSet = callableStatement.getResultSet();
//
//        // 获取元数据
//        ResultSetMetaData column = resultSet.getMetaData();
//        while (resultSet.next()) {
//          Map<String, String> map = new HashMap<>();
//          for (int i = 1; i <= column.getColumnCount(); i++) {
//            map.put(column.getColumnName(i), resultSet.getString(i));
//          }
//          result.add(map);
//        }
//      }
//      // 处理异常
//      catch (Exception e) {
//        logger.error("查询失败，sql: SET NOCOUNT ON exec proc_wz_balancequery_single ?,?,?", e);
//      }
//      // 关闭链接
//      finally {
//        if (resultSet != null) {
//          try {
//            resultSet.close();
//          } catch (Exception e) {
//            e.printStackTrace();
//          }
//        }
//
//        if (callableStatement != null) {
//          try {
//            callableStatement.close();
//          } catch (Exception e) {
//            e.printStackTrace();
//          }
//        }
//
//        try {
//          connection.close();
//        } catch (Exception e) {
//          e.printStackTrace();
//        }
//      }
//    }
//    return result;
//  }
}
